<?php
 
	/*
	 * Created by Nicky
	 *
	*/
	 /* permission name 
	 karPenilaian_Student
	 karPenilaian_Student_add
	 karPenilaian_Student_edit
	 */
	class Report_nilaiController extends CController{
		private $result = array("actionStatus"=>0,"actionMessage"=>"","model"=>array(),"actionError"=>array());	
		public $mainMenu = "Karyawan";
		public $subMenu = "";
		public $title = "";		
		public $loginUser;		
		public function init() {
			parent::init();
			if (!LoginUser::isLogin("karyawan")){
				LoginUser::redirectToLoginPage("karyawan");
			}else{
				$this->loginUser = LoginUser::getUser();
			}
						
		}
		
		//PelajaranControler
		public function actionByPelajaran(){
			LoginUser::getPermission("karyawan_penilaian_student");
			$this->subMenu = "Penilaian Pelajaran";
			$this->layout ="//layouts/ly_blank";
			$karyawan = $this->loginUser ->karyawan;
			$karyawan_id=$karyawan->id;	
			$department_id = $karyawan->department_id;	
			$department_name = $karyawan->department->name;					
			$karyawan_name = $karyawan->name;						
 			$module= $this->loginUser ->module;
 
  			if ($module=="akademik")
			{
			$departments = VJadwalPelajaranKelasTahunAjaran::model()->findAll("is_active = TRUE    group by department_id order by id");
			} else 
			{
			$departments = VJadwalPelajaranKelasTahunAjaran::model()->findAll("is_active = TRUE and department_id=:departmentId  group by department_id order by id",array(":departmentId"=>$department_id));
			}

			if ($departments == null){
				$department_id == "";
			}else{	
				$department_id = Yii::app()->request->getParam("department_id", $departments[0]->department_id);
			}
			
			//$department_id = $department_id == "" ? $departments[0]->id : $department_id;					
//			$tahunAjarans  = MTahunAjaran::model()->findAll("department_id=:departmentId and is_active = TRUE order by id desc",array(":departmentId"=>$department_id));
	 		$tahun_ajaran_id = Yii::app()->request->getParam("tahun_ajaran_id","");	
	 		$semester_id = Yii::app()->request->getParam("semester_id","");	
	 		$tingkatan_id = Yii::app()->request->getParam("tingkatan_id","");				
	 		$kelas_tahun_ajaran_id = Yii::app()->request->getParam("kelas_tahun_ajaran_id","");			
	 		$pelajaran_id = Yii::app()->request->getParam("pelajaran_id","");		
	 		$walikelas_id = Yii::app()->request->getParam("walikelas_id","");					
			 									
			$tahunAjarans = VJadwalPelajaranKelasTahunAjaran::model()->findAll("is_active = TRUE and department_id=:departmentId   group by tahun_ajaran_id order by tahun_ajaran_id desc  ",array(":departmentId"=>$department_id));			
			if ($tahunAjarans == null){
				$tahun_ajaran_id == "";
			}else{
				$tahun_ajaran_id = $tahun_ajaran_id == "" ? $tahunAjarans[0]->tahun_ajaran_id : $tahun_ajaran_id;
			}			
			$semesters = MSemester::model()->findAll("tahun_ajaran_id=:tahun_ajaran_id and is_active = TRUE and department_id='".$department_id."' order by id",array(":tahun_ajaran_id"=>$tahun_ajaran_id));
			//$semester_id = "";
			if ($semesters == null){
				$semester_id == "";
			}else{
				$semester_id = $semester_id == "" ? $semesters[0]->id : $semester_id;
			}
            $tingkatans =  VJadwalPelajaranKelasTahunAjaran::model()->findAll("is_active = TRUE and department_id=:departmentId and tahun_ajaran_id=:tahun_ajaran_id  group by tingkatan_id order by id",array(":departmentId"=>$department_id,":tahun_ajaran_id"=>$tahun_ajaran_id));
//            $tingkatan_id = $tingkatans[0]->id ; 
			if ($tingkatans == null){
				$tingkatan_id == "";
			}else{
				$tingkatan_id = $tingkatan_id == "" ? $tingkatans[0]->tingkatan_id : $tingkatan_id;
			}
			
			$mcon= Yii::app()->db;						
			$kelass = VJadwalPelajaranKelasTahunAjaran::model()->findAll("tingkatan_id=:tingkatan_id and department_id=:departmentId and tahun_ajaran_id=:tahun_ajaran_id  and is_active = TRUE group by kelas_id,tingkatan_id order by id",array(":tingkatan_id"=>$tingkatan_id,":departmentId"=>$department_id,":tahun_ajaran_id"=>$tahun_ajaran_id));
 
			if ($kelass == null){
				$kelas_tahun_ajaran_id == "";
			}else{
				$kelas_tahun_ajaran_id = $kelas_tahun_ajaran_id == "" ? $kelass[0]["kelas_tahun_ajaran_id"] : $kelas_tahun_ajaran_id;
			}
						
			$walikelass = VJadwalPelajaranKelasTahunAjaran::model()->findAll("department_id=:departmentId and tahun_ajaran_id=:tahun_ajaran_id  and kelas_tahun_ajaran_id=:kelas_tahun_ajaran_id and wali_kelas_id=:karyawanId and is_active = TRUE group by kelas_id,tingkatan_id order by id desc",array(":karyawanId"=>$karyawan_id,":kelas_tahun_ajaran_id"=>$kelas_tahun_ajaran_id,":departmentId"=>$department_id,":tahun_ajaran_id"=>$tahun_ajaran_id));
			$walikelas_id = count($walikelass);
 
			if (($walikelas_id >0)){
	 $pelajarans = Yii::app()->db->createCommand()
				->select('P.id,name,kelas_tahun_ajaran_id')
				->from('m_jadwal_pelajaran JP')
				->join('m_pelajaran P', 'JP.pelajaran_id = P.id')
				->where('kelas_tahun_ajaran_id=:kelas_tahun_ajaran_id', array(':kelas_tahun_ajaran_id'=>$kelas_tahun_ajaran_id))
				->queryAll();
				}
				else
				{
	 $pelajarans = Yii::app()->db->createCommand()
				->select('P.id,name,kelas_tahun_ajaran_id')
				->from('m_jadwal_pelajaran JP')
				->join('m_pelajaran P', 'JP.pelajaran_id = P.id')
				->where('kelas_tahun_ajaran_id=:kelas_id and karyawan_id=:karyawan_id', array(':kelas_id'=>$kelas_tahun_ajaran_id,':karyawan_id'=>$karyawan_id))
				->queryAll();
				}			
	if ($module=="akademik"){
	 $pelajarans = Yii::app()->db->createCommand()
				->select('P.id,name,kelas_tahun_ajaran_id')
				->from('m_jadwal_pelajaran JP')
				->join('m_pelajaran P', 'JP.pelajaran_id = P.id')
				->where('kelas_tahun_ajaran_id=:kelas_id', array(':kelas_id'=>$kelas_tahun_ajaran_id))
				->queryAll();
				}					
		 
			if ($pelajarans == null){
				$pelajaran_id == "";
			}else{
			
				$pelajaran_id = $pelajaran_id == "" ? $pelajarans[0]["id"] : $pelajaran_id;
			}
			
			
						
			//query view data
			$mcon= Yii::app()->db; 
			
$sqlall='SELECT S.id as student_id,S.sid as sid, S.name as student_name, VKTA.id as kelas_tahun_ajaran_id,kelas_id,VKTA.tahun_ajaran_id,tingkatan_id,tingkatan_name,VKTA.department_id,wali_kelas_id,kelas_name, JP.pelajaran_id 
,case when NP.id is null then "0" else NP.nilai end  as nilai
,NP.task_detail_id as task_detail_id,NP.name as task_detail_name, NP.notes, NP.task_date, NP.task_name as task_name, NP.task_group,NP.semester_id
FROM  v_kelas_tahun_ajaran VKTA
LEFT JOIN m_student_class SC on SC.kelas_tahun_ajaran_id=VKTA.id
LEFT JOIN m_student S on SC.student_id=S.id 
LEFT JOIN 
(SELECT JP.kelas_tahun_ajaran_id, JP.pelajaran_id 
FROM `m_jadwal_pelajaran` JP) JP on JP.kelas_tahun_ajaran_id=VKTA.id
left outer join 
(SELECT P.id, P.nilai,P.student_id,TPD.kelas_tahun_ajaran_id,TP.pelajaran_id,TPD.name, TPD.notes, TPD.task_date, TP.name as task_name, TP.task_group,(SELECT id FROM m_semester WHERE TPD.task_date BETWEEN start_date AND end_date LIMIT 1 ) as semester_id,TPD.id as task_detail_id
FROM `m_penilaian` P
left join m_task_pelajaran_detail TPD on P.task_pelajaran_detail_id=TPD.id
left join m_task_pelajaran TP on TPD.task_pelajaran_id=TP.id
where TP.is_active=true ) NP on (NP.student_id=S.id and NP.pelajaran_id=JP.pelajaran_id and NP.kelas_tahun_ajaran_id=VKTA.id )
';
$where_taskgroup=' where task_group IS NOT NULL  and semester_id=:semester_id and VKTA.tahun_ajaran_id=:tahun_ajaran_id and VKTA.id=:kelas_tahun_ajaran_id  and VKTA.department_id=:department_id and JP.pelajaran_id=:pelajaran_id';
$where_student=' where S.id IS NOT NULL  and VKTA.tahun_ajaran_id=:tahun_ajaran_id and VKTA.id=:kelas_tahun_ajaran_id  and VKTA.department_id=:department_id';
$sqlall_task=$sqlall.$where_taskgroup; 
$sqlall_student=$sqlall.$where_student; 

 		$viewdata="";
/*
		$sqltaskname = $sqlall_student . " group by student_id";
		$m_students=$mcon->createCommand($sqltaskname);
		$m_students->bindValue('tahun_ajaran_id',$tahun_ajaran_id);
		$m_students->bindValue('kelas_tahun_ajaran_id',$kelas_tahun_ajaran_id);
		$m_students->bindValue('department_id',$department_id);						
		$m_students=$m_students->queryAll();
		$viewdata="";
		foreach ($m_students as $m_student){	
			$student_id	= $m_student["student_id"];	
			$viewdata .= "STUDENT'S NAME : " . $m_student["student_name"] . "<BR>STUDENT'S NUMBER : " .$m_student["sid"]."<BR>CLASS : ".	$m_student["tingkatan_name"];	$m_student["kelas_name"].'<br><br>';		
			*/			
 			$sqltaskname = $sqlall_task . " group by task_group";
			$mPenilaianCommands=$mcon->createCommand($sqltaskname);
			$mPenilaianCommands->bindValue('tahun_ajaran_id',$tahun_ajaran_id);
			$mPenilaianCommands->bindValue('kelas_tahun_ajaran_id',$kelas_tahun_ajaran_id);
			$mPenilaianCommands->bindValue('semester_id',$semester_id);	
			$mPenilaianCommands->bindValue('department_id',$department_id);			
			$mPenilaianCommands->bindValue('pelajaran_id',$pelajaran_id);							
			$mPenilaianCommands=$mPenilaianCommands->queryAll();
			$rowCount = count($mPenilaianCommands);
			if ($rowCount==0){			  							
			$viewdata .="<table class=nilai><tr><td >Student</td>";
			}
			else
			{			$viewdata .="<table class=nilai><tr><td rowspan=2>Student</td>"; }
			foreach ($mPenilaianCommands as $mPenilaianCommand){
				$sqltask_count = $sqlall_task . " and task_group =:task_group group by task_group,semester_id, task_detail_name";
				$mPenilaianCommandsCountings=$mcon->createCommand($sqltask_count);		
				$mPenilaianCommandsCountings->bindValue('task_group',$mPenilaianCommand["task_group"]);				
				$mPenilaianCommandsCountings->bindValue('tahun_ajaran_id',$tahun_ajaran_id);
				$mPenilaianCommandsCountings->bindValue('kelas_tahun_ajaran_id',$kelas_tahun_ajaran_id);
				$mPenilaianCommandsCountings->bindValue('semester_id',$semester_id);	
				$mPenilaianCommandsCountings->bindValue('department_id',$department_id);			
				$mPenilaianCommandsCountings->bindValue('pelajaran_id',$pelajaran_id);													
				$mPenilaianCommandsCountings=$mPenilaianCommandsCountings->queryAll();
				$rowCount = count($mPenilaianCommandsCountings);
				$viewdata .= '<td colspan='.$rowCount.'>'.$mPenilaianCommand["task_name"].'</td>';   				
	 		}
	 		
				
				$viewdata .= '</tr>';
			$setcount=0;
			$setarray = array();
			foreach ($mPenilaianCommands as $mPenilaianCommand){
				$sqltask_count = $sqlall_task . " and task_group =:task_group  group by task_group,semester_id, task_detail_name order by task_detail_id";
				$mPenilaianCommandsCountings=$mcon->createCommand($sqltask_count);		
				$mPenilaianCommandsCountings->bindValue('task_group',$mPenilaianCommand["task_group"]);				
				$mPenilaianCommandsCountings->bindValue('tahun_ajaran_id',$tahun_ajaran_id);
				$mPenilaianCommandsCountings->bindValue('kelas_tahun_ajaran_id',$kelas_tahun_ajaran_id);
				$mPenilaianCommandsCountings->bindValue('semester_id',$semester_id);	
				$mPenilaianCommandsCountings->bindValue('department_id',$department_id);	
				$mPenilaianCommandsCountings->bindValue('pelajaran_id',$pelajaran_id);																
				$mPenilaianCommandsCountings=$mPenilaianCommandsCountings->queryAll();
				$rowCount = count($mPenilaianCommandsCountings);
//				$setarray[] = $mPenilaianCommand['task_group'];
				 for ($xid=1; $xid<$rowCount+1; $xid++)
				 {
					$viewdata .= '<td  >'.$xid.'</td>';   				
				 }
				 foreach ($mPenilaianCommandsCountings as $mPenilaianCommandsCounting){
					 $setarray[$mPenilaianCommand['task_group']][] = $mPenilaianCommandsCounting['task_detail_id'];
				 }
				$setcount= $setcount+1;				 
	 		}
				$viewdata .= '</tr>';
/*				$sqlpel='SELECT VKTA.id as kelas_tahun_ajaran_id, karyawan_id, pelajaran_id,name, tahun_ajaran_id, kelas_id, wali_kelas_id, tingkatan_id, VKTA.department_id FROM `m_jadwal_pelajaran` JP
				left join v_kelas_tahun_ajaran VKTA
				on JP.kelas_tahun_ajaran_id=VKTA.id
				left join m_pelajaran P
				on P.id = pelajaran_id where   VKTA.tahun_ajaran_id=:tahun_ajaran_id and VKTA.id=:kelas_tahun_ajaran_id and VKTA.department_id=:department_id ';				
				$sqlpel = $sqlpel . "   group by pelajaran_id";
				$mpelajarans=$mcon->createCommand($sqlpel);		
				$mpelajarans->bindValue('tahun_ajaran_id',$tahun_ajaran_id);
				$mpelajarans->bindValue('kelas_tahun_ajaran_id',$kelas_tahun_ajaran_id);
				$mpelajarans->bindValue('department_id',$department_id);							
				$mpelajarans=$mpelajarans->queryAll();		
*/ 			
				$inCounter=0;$xid=0;
//				foreach ($mpelajarans as $mpelajaran){
//				$pelajaran_id=$mpelajaran["pelajaran_id"];
		$sqltaskname = $sqlall_student . " group by student_id";
		$m_students=$mcon->createCommand($sqltaskname);
		$m_students->bindValue('tahun_ajaran_id',$tahun_ajaran_id);
		$m_students->bindValue('kelas_tahun_ajaran_id',$kelas_tahun_ajaran_id);
		$m_students->bindValue('department_id',$department_id);						
		$m_students=$m_students->queryAll();
		//$viewdata="";
		foreach ($m_students as $m_student){
				$student_id	= $m_student["student_id"];	
				$viewdata .=  '<tr><td>'.$m_student["student_name"].'</td>';
				 foreach ($mPenilaianCommands as $mPenilaianCommand){
				 	$rowCount=count($setarray[$mPenilaianCommand["task_group"]]);
					for ($xid=0; $xid<$rowCount; $xid++)
					{
					 $sqlpenilaian = $sqlall_task . "  and JP.pelajaran_id =:pelajaran_id and S.id =:student_id  and NP.task_detail_id=:task_detail_id  order by task_detail_id";
					$mpelajarans_nilai=$mcon->createCommand($sqlpenilaian);		
					$setnilai=$setarray[$mPenilaianCommand["task_group"]][$xid];
					$mpelajarans_nilai->bindValue('task_detail_id',$setnilai);		
					$mpelajarans_nilai->bindValue('student_id',$student_id);
					$mpelajarans_nilai->bindValue('pelajaran_id',$pelajaran_id);
					$mpelajarans_nilai->bindValue('tahun_ajaran_id',$tahun_ajaran_id);
					$mpelajarans_nilai->bindValue('kelas_tahun_ajaran_id',$kelas_tahun_ajaran_id);
					$mpelajarans_nilai->bindValue('semester_id',$semester_id);	
					$mpelajarans_nilai->bindValue('department_id',$department_id);							
					$mpelajarans_nilai=$mpelajarans_nilai->queryAll();	
					$rowCountsub = count($mpelajarans_nilai);
					if ($rowCountsub==0){$viewdata .=  ' <td>&nbsp;</td>';}
					else {
					foreach ($mpelajarans_nilai as $mpelajaran_nilai){
							$viewdata .=  ' <td>'.$mpelajaran_nilai["nilai"].'</td>';
						}}					
					}
 
				 }
				$viewdata .= '</tr>';
				}	
					$viewdata .="</table><br>";		
			//}
			
	 
		 
		 	 
			
			$data= array(
					"department_id"=>$department_id,
					"departments" => $departments,						
					"karyawan_name"=>$karyawan_name,
					"karyawan_id"=>$karyawan_id,										
					"tahun_ajaran_id"=>$tahun_ajaran_id,
					"tingkatans"=>$tingkatans,		
					"tingkatan_id"=>$tingkatan_id,					
					"tahunAjarans"=>$tahunAjarans,					
					"semesters"=>$semesters,
					"semester_id" => $semester_id,
					"kelass"=>$kelass,
					"kelas_tahun_ajaran_id"=>$kelas_tahun_ajaran_id,
					"pelajarans"=>$pelajarans,
					"pelajaran_id"=>$pelajaran_id,	
					"walikelas_id"=>$walikelas_id,										
					"viewdata" =>$viewdata

			);
			$this->render('rptByPelajaran',$data);		
		}
 	
		
		//StudentController
		public function actionByStudent(){
			LoginUser::getPermission("karyawan_penilaian_student");
			$this->subMenu = "Penilaian";
			$this->layout ="//layouts/ly_blank";
			$karyawan = $this->loginUser ->karyawan;
			$karyawan_id=$karyawan->id;
			$department_id = $karyawan->department_id;
			$department_name = $karyawan->department->name;
			$karyawan_name = $karyawan->name;
		
			//$department_id = $department_id == "" ? $departments[0]->id : $department_id;
			$tahun_ajaran_id = Yii::app()->request->getParam("tahun_ajaran_id","");
			$semester_id = Yii::app()->request->getParam("semester_id","");
			$kelas_tahun_ajaran_id = Yii::app()->request->getParam("kelas_tahun_ajaran_id","");
	 		$tingkatan_id = Yii::app()->request->getParam("tingkatan_id","");			
	 		$walikelas_id = Yii::app()->request->getParam("walikelas_id","");	
	 		$studentid = Yii::app()->request->getParam("studentid","0");					
									
			$tahunAjarans = VJadwalPelajaranKelasTahunAjaran::model()->findAll("is_active = TRUE and department_id=:departmentId   group by tahun_ajaran_id order by tahun_ajaran_id desc  ",array(":departmentId"=>$department_id));			
			if ($tahunAjarans == null){
				$tahun_ajaran_id == "";
			}else{
				$tahun_ajaran_id = $tahun_ajaran_id == "" ? $tahunAjarans[0]->tahun_ajaran_id : $tahun_ajaran_id;
			}	
			$semesters = MSemester::model()->findAll("tahun_ajaran_id=:tahun_ajaran_id and is_active = TRUE and department_id='".$department_id."' order by id",array(":tahun_ajaran_id"=>$tahun_ajaran_id));
			//$semester_id = "";
			if ($semesters == null){
				$semester_id == "";
			}else{
				$semester_id = $semester_id == "" ? $semesters[0]->id : $semester_id;
			}
            $tingkatans =  VJadwalPelajaranKelasTahunAjaran::model()->findAll("is_active = TRUE and department_id=:departmentId and tahun_ajaran_id=:tahun_ajaran_id  group by tingkatan_id order by id",array(":departmentId"=>$department_id,":tahun_ajaran_id"=>$tahun_ajaran_id));
//            $tingkatan_id = $tingkatans[0]->id ; 
			if ($tingkatans == null){
				$tingkatan_id == "";
			}else{
				$tingkatan_id = $tingkatan_id == "" ? $tingkatans[0]->tingkatan_id : $tingkatan_id;
			}
			
			$walikelass = VJadwalPelajaranKelasTahunAjaran::model()->findAll("department_id=:departmentId and tahun_ajaran_id=:tahun_ajaran_id  and kelas_tahun_ajaran_id=:kelas_tahun_ajaran_id and wali_kelas_id=:karyawanId and is_active = TRUE group by kelas_id,tingkatan_id order by id desc",array(":karyawanId"=>$karyawan_id,":kelas_tahun_ajaran_id"=>$kelas_tahun_ajaran_id,":departmentId"=>$department_id,":tahun_ajaran_id"=>$tahun_ajaran_id));
			$walikelas_id = count($walikelass);
						
			$mcon= Yii::app()->db;						
			
			$kelass = VJadwalPelajaranKelasTahunAjaran::model()->findAll("tingkatan_id=:tingkatan_id and department_id=:departmentId and tahun_ajaran_id=:tahun_ajaran_id  and is_active = TRUE  and wali_kelas_id=:karyawanId group by kelas_id,tingkatan_id order by id",array(":karyawanId"=>$karyawan_id,":tingkatan_id"=>$tingkatan_id,":departmentId"=>$department_id,":tahun_ajaran_id"=>$tahun_ajaran_id));
 
			if ($kelass == null){
				$kelas_tahun_ajaran_id == "";
			}else{
				$kelas_tahun_ajaran_id = $kelas_tahun_ajaran_id == "" ? $kelass[0]["kelas_tahun_ajaran_id"] : $kelas_tahun_ajaran_id;
			}
			
			$students = VStudentKelasTahunAjaran::model()->findAll("department_id=:departmentId and tahun_ajaran_id=:tahun_ajaran_id  and is_active = TRUE  and kelas_tahun_ajaran_id=:kelas_tahun_ajaran_id ",array(":kelas_tahun_ajaran_id"=>$kelas_tahun_ajaran_id,":departmentId"=>$department_id,":tahun_ajaran_id"=>$tahun_ajaran_id));
 
			if ($students == null){
				$studentid == "";
			}else{
				$studentid = $studentid == "" ? "" : $studentid;
			}			
		 
	 
			//query view data
			$mcon= Yii::app()->db;
				
			$sqlall='SELECT S.id as student_id,S.sid as sid, S.name as student_name, VKTA.id as kelas_tahun_ajaran_id,kelas_id,VKTA.tahun_ajaran_id,tingkatan_id,tingkatan_name,VKTA.department_id,wali_kelas_id,kelas_name, JP.pelajaran_id
,case when NP.id is null then "0" else NP.nilai end  as nilai
,NP.task_detail_id as task_detail_id,NP.name as task_detail_name, NP.notes, NP.task_date, NP.task_name as task_name, NP.task_group,NP.semester_id
FROM  v_kelas_tahun_ajaran VKTA
LEFT JOIN m_student_class SC on SC.kelas_tahun_ajaran_id=VKTA.id
LEFT JOIN m_student S on SC.student_id=S.id
LEFT JOIN
(SELECT JP.kelas_tahun_ajaran_id, JP.pelajaran_id
FROM `m_jadwal_pelajaran` JP) JP on JP.kelas_tahun_ajaran_id=VKTA.id
left outer join
(SELECT P.id, P.nilai,P.student_id,TPD.kelas_tahun_ajaran_id,TP.pelajaran_id,TPD.name, TPD.notes, TPD.task_date, TP.name as task_name, TP.task_group,(SELECT id FROM m_semester WHERE TPD.task_date BETWEEN start_date AND end_date LIMIT 1 ) as semester_id,TPD.id as task_detail_id
FROM `m_penilaian` P
left join m_task_pelajaran_detail TPD on P.task_pelajaran_detail_id=TPD.id
left join m_task_pelajaran TP on TPD.task_pelajaran_id=TP.id
where TP.is_active=true ) NP on (NP.student_id=S.id and NP.pelajaran_id=JP.pelajaran_id and NP.kelas_tahun_ajaran_id=VKTA.id )
';
			$where_taskgroup=' where task_group IS NOT NULL  and semester_id=:semester_id and VKTA.tahun_ajaran_id=:tahun_ajaran_id and VKTA.id=:kelas_tahun_ajaran_id  and VKTA.department_id=:department_id';
			if ($studentid>0){
				$where_student=' where S.id IS NOT NULL  and VKTA.tahun_ajaran_id=:tahun_ajaran_id and VKTA.id=:kelas_tahun_ajaran_id  and VKTA.department_id=:department_id and S.id='.$studentid;	
			}
			else
			{
				$where_student=' where S.id IS NOT NULL  and VKTA.tahun_ajaran_id=:tahun_ajaran_id and VKTA.id=:kelas_tahun_ajaran_id  and VKTA.department_id=:department_id'; 		
			}
			$sqlall_task=$sqlall.$where_taskgroup;
			$sqlall_student=$sqlall.$where_student;
		
		
		
			$sqltaskname = $sqlall_student . " group by student_id";
			$m_students=$mcon->createCommand($sqltaskname);
			$m_students->bindValue('tahun_ajaran_id',$tahun_ajaran_id);
			$m_students->bindValue('kelas_tahun_ajaran_id',$kelas_tahun_ajaran_id);
			$m_students->bindValue('department_id',$department_id);
			$m_students=$m_students->queryAll();
			$viewdata="";
			foreach ($m_students as $m_student){
				$student_id	= $m_student["student_id"];
				$viewdata .= "STUDENT'S NAME : " . $m_student["student_name"] . "<BR>STUDENT'S NUMBER : " .$m_student["sid"]."<BR>CLASS : ".	$m_student["tingkatan_name"];	$m_student["kelas_name"].'<br><br>';
				$sqltaskname = $sqlall_task . " group by task_group";
				$mPenilaianCommands=$mcon->createCommand($sqltaskname);
				$mPenilaianCommands->bindValue('tahun_ajaran_id',$tahun_ajaran_id);
				$mPenilaianCommands->bindValue('kelas_tahun_ajaran_id',$kelas_tahun_ajaran_id);
				$mPenilaianCommands->bindValue('semester_id',$semester_id);
				$mPenilaianCommands->bindValue('department_id',$department_id);
				$mPenilaianCommands=$mPenilaianCommands->queryAll();
				$viewdata .="<table class=nilai><tr><td rowspan=2>Pelajaran</td>";
				foreach ($mPenilaianCommands as $mPenilaianCommand){
					$sqltask_count = $sqlall_task . " and task_group =:task_group group by task_group,semester_id, task_detail_name";
					$mPenilaianCommandsCountings=$mcon->createCommand($sqltask_count);
					$mPenilaianCommandsCountings->bindValue('task_group',$mPenilaianCommand["task_group"]);
					$mPenilaianCommandsCountings->bindValue('tahun_ajaran_id',$tahun_ajaran_id);
					$mPenilaianCommandsCountings->bindValue('kelas_tahun_ajaran_id',$kelas_tahun_ajaran_id);
					$mPenilaianCommandsCountings->bindValue('semester_id',$semester_id);
					$mPenilaianCommandsCountings->bindValue('department_id',$department_id);
					$mPenilaianCommandsCountings=$mPenilaianCommandsCountings->queryAll();
					$rowCount = count($mPenilaianCommandsCountings);
					$viewdata .= '<td colspan='.$rowCount.'>'.$mPenilaianCommand["task_name"].'</td>';
				}
				$viewdata .= '</tr>';
				$setcount=0;
				$setarray = array();
				foreach ($mPenilaianCommands as $mPenilaianCommand){
					$sqltask_count = $sqlall_task . " and task_group =:task_group  group by task_group,semester_id, task_detail_name order by task_detail_id";
					$mPenilaianCommandsCountings=$mcon->createCommand($sqltask_count);
					$mPenilaianCommandsCountings->bindValue('task_group',$mPenilaianCommand["task_group"]);
					$mPenilaianCommandsCountings->bindValue('tahun_ajaran_id',$tahun_ajaran_id);
					$mPenilaianCommandsCountings->bindValue('kelas_tahun_ajaran_id',$kelas_tahun_ajaran_id);
					$mPenilaianCommandsCountings->bindValue('semester_id',$semester_id);
					$mPenilaianCommandsCountings->bindValue('department_id',$department_id);
					$mPenilaianCommandsCountings=$mPenilaianCommandsCountings->queryAll();
					$rowCount = count($mPenilaianCommandsCountings);
					//				$setarray[] = $mPenilaianCommand['task_group'];
					for ($xid=1; $xid<$rowCount+1; $xid++)
					{
					$viewdata .= '<td  >'.$xid.'</td>';
					}
					foreach ($mPenilaianCommandsCountings as $mPenilaianCommandsCounting){
					$setarray[$mPenilaianCommand['task_group']][] = $mPenilaianCommandsCounting['task_detail_id'];
				 }
				$setcount= $setcount+1;
				}
				$viewdata .= '</tr>';
					$sqlpel='SELECT VKTA.id as kelas_tahun_ajaran_id, karyawan_id, pelajaran_id,name, tahun_ajaran_id, kelas_id, wali_kelas_id, tingkatan_id, VKTA.department_id FROM `m_jadwal_pelajaran` JP
				left join v_kelas_tahun_ajaran VKTA
				on JP.kelas_tahun_ajaran_id=VKTA.id
				left join m_pelajaran P
				on P.id = pelajaran_id where   VKTA.tahun_ajaran_id=:tahun_ajaran_id and VKTA.id=:kelas_tahun_ajaran_id and VKTA.department_id=:department_id ';
				$sqlpel = $sqlpel . "   group by pelajaran_id";
						$mpelajarans=$mcon->createCommand($sqlpel);
						$mpelajarans->bindValue('tahun_ajaran_id',$tahun_ajaran_id);
				$mpelajarans->bindValue('kelas_tahun_ajaran_id',$kelas_tahun_ajaran_id);
				$mpelajarans->bindValue('department_id',$department_id);
						$mpelajarans=$mpelajarans->queryAll();
		
					$inCounter=0;$xid=0;
					foreach ($mpelajarans as $mpelajaran){
					$pelajaran_id=$mpelajaran["pelajaran_id"];
					$viewdata .=  '<tr><td>'.$mpelajaran["name"].'</td>';
					foreach ($mPenilaianCommands as $mPenilaianCommand){
					$rowCount=count($setarray[$mPenilaianCommand["task_group"]]);
					for ($xid=0; $xid<$rowCount; $xid++)
					{
							$sqlpenilaian = $sqlall_task . "  and JP.pelajaran_id =:pelajaran_id and S.id =:student_id  and NP.task_detail_id=:task_detail_id  order by task_detail_id";
					$mpelajarans_nilai=$mcon->createCommand($sqlpenilaian);
							$setnilai=$setarray[$mPenilaianCommand["task_group"]][$xid];
							$mpelajarans_nilai->bindValue('task_detail_id',$setnilai);
					$mpelajarans_nilai->bindValue('student_id',$student_id);
					$mpelajarans_nilai->bindValue('pelajaran_id',$pelajaran_id);
					$mpelajarans_nilai->bindValue('tahun_ajaran_id',$tahun_ajaran_id);
					$mpelajarans_nilai->bindValue('kelas_tahun_ajaran_id',$kelas_tahun_ajaran_id);
					$mpelajarans_nilai->bindValue('semester_id',$semester_id);
					$mpelajarans_nilai->bindValue('department_id',$department_id);
					$mpelajarans_nilai=$mpelajarans_nilai->queryAll();
					$rowCountsub = count($mpelajarans_nilai);
					if ($rowCountsub==0){$viewdata .=  ' <td>&nbsp;</td>';}
					else {
					foreach ($mpelajarans_nilai as $mpelajaran_nilai){
					$viewdata .=  ' <td>'.$mpelajaran_nilai["nilai"].'</td>';
					}}
					}
		
					}
					$viewdata .= '</tr>';
					}
							$viewdata .="</table><br>";
			}
						
		
						
					 
						
					$data= array(
					"department_id"=>$department_id,
					"karyawan_name"=>$karyawan_name,
					"karyawan_id"=>$karyawan_id,
					"tahun_ajaran_id"=>$tahun_ajaran_id,
					"tingkatans"=>$tingkatans,		
					"tingkatan_id"=>$tingkatan_id,	
					"tahunAjarans"=>$tahunAjarans,
					"semesters"=>$semesters,
					"semester_id" => $semester_id,
					"kelass"=>$kelass,
					"kelas_tahun_ajaran_id"=>$kelas_tahun_ajaran_id,
					"department_name"=>$department_name,
					"students"=>$students,
					"walikelas_id"=>$walikelas_id,			
					"studentid"=>$studentid,			
					"viewdata" =>$viewdata
		
			);
					$this->render('rptByStudent',$data);
		}
		
		
		protected function beforeRender($view){
			if ($this->subMenu != ""){
				$this->title = $this->mainMenu . " - " .$this->subMenu;
			}
			return true;
		}
		
	public function actionPopupWindow()
	{
		$this->layout = "//layouts/ly_blank";
		$this->render('popupWindow');
	}		
		
	}
?>